package com.zr.daoImpl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.zr.dao.RecordDao;
import com.zr.entity.Record;
import com.zr.utils.DBUtils;
import com.zr.utils.RecordFlag;
import com.zr.utils.SystemFlag;

public class RecordDaoImpl extends DBUtils implements RecordDao{

	@Override
	public List<Object> queryAllFriend(String uid) throws Exception {
		String sql="select t4.*,t5.count,`user`.nickname,user.user_head FROM (select rd.* from record as rd INNER JOIN (select t1.* from (select sender,getter,max(create_time) as ct from record where sender <> ? GROUP BY sender,getter ORDER BY max(create_time) DESC) as t1 LEFT JOIN (select sender,getter,max(create_time) as ct from record where sender <> ? GROUP BY sender,getter ORDER BY max(create_time) DESC) t2 on t1.sender = t2.getter and t1.getter = t2.sender where t1.ct > t2.ct or t2.ct is NULL) as t3 on rd.sender = t3.sender and rd.create_time = t3.ct ORDER BY create_time DESC) t4 LEFT JOIN (select sender,count(sender) as count from record where getter = ? and status = ? and sender <> ?  and message <> \"\" GROUP BY sender) t5 on t4.sender = t5.sender or t4.getter= t5.sender LEFT JOIN `user` on t4.sender = `user`.uid and t4.sender <> ? or t4.getter = `user`.uid and t4.getter <> ?";
		Object [] objs= {RecordFlag.STATUS_OFFICIAL,RecordFlag.STATUS_OFFICIAL,uid,RecordFlag.STATUS_NOTREAD,RecordFlag.STATUS_OFFICIAL,uid,uid};
		List<Object> rlist=new ArrayList<Object>();
		ResultSet rs=query(sql,objs);
		while(rs.next()) {
			Record rd = new Record(rs.getString("message"),rs.getString("sender"),rs.getString("getter"),rs.getInt("status"),rs.getString("create_time"),rs.getString("nickname"),rs.getString("user_head"));
			rd.setNotReadCount(rs.getInt("count"));
			if(rd.getSender().equals(uid)) {
				rd.setSender(rd.getGetter());
			}
			rlist.add(rd);
		}
		return rlist;
	}
	
	@Override
	public List<Object> queryAllMessage(String uid,String sender) throws Exception {
		String sql="SELECT * from record where sender = ? AND getter = ? UNION select * from record where sender = ? and getter = ? ORDER BY create_time ASC";
		Object [] objs= {uid,sender,sender,uid};
		List<Object> rlist=new ArrayList<Object>();
		ResultSet rs=query(sql,objs);
		while(rs.next()) {
			Record rd = new Record(rs.getString("message"),rs.getString("sender"),rs.getString("getter"),rs.getInt("status"),rs.getString("create_time"),null,null);
			rlist.add(rd);
		}
//		System.out.println(rlist);
		return rlist;
	}
	@Override
	public int updateStatus(String uid, String sender) throws Exception {
		String sql = "update record set status = ? where (sender = ? and getter = ?) or (sender = ? and getter = ?)";
		Object [] objs = {RecordFlag.STATUS_READ,uid,sender,sender,uid};
		return modify(sql,objs);
	}
	
	

	@Override
	public int sendMessage(String uid, String getter, String message) throws Exception {
		String sql = "insert into Record values(?,?,?,?,now())";
		Object [] objs = {message,uid,getter,RecordFlag.STATUS_NOTREAD};
		return modify(sql,objs);
	}

	@Override
	public List<Object> queryAllOfficial(String uid) throws Exception {
		String sql="select * from record where sender = ? and getter in(0,?) ORDER BY create_time DESC";
		Object [] objs= {RecordFlag.STATUS_OFFICIAL,uid};
		List<Object> rlist=new ArrayList<Object>();
		ResultSet rs=query(sql,objs);
		while(rs.next()) {
			Record rd = new Record(rs.getString("message"),rs.getString("sender"),rs.getString("getter"),rs.getInt("status"),rs.getString("create_time"),null,null);
			rlist.add(rd);
		}
		return rlist;
	}

	@Override
	public int newMessageByNickname(String uid, String nickname) throws Exception {
		String sql = "INSERT into record values('',(select uid from user where nickname = ?),?,1,NOW())";
		Object [] objs = {nickname,uid};
		return modify(sql,objs);
	}

	@Override
	public int getSysNotRead(String uid) throws Exception {
		System.out.println(uid);
		String sql="select count(*) as count from record where sender = ? and status = ? and (getter = 0 or getter = ?)";
		Object [] objs= {RecordFlag.STATUS_OFFICIAL,RecordFlag.STATUS_NOTREAD,uid};
		ResultSet rs=query(sql,objs);
		int count = 0;
		if(rs.next()) {
			count = rs.getInt("count");
		}
		return count;
	}

	@Override
	public int setSysRead() throws Exception {
		String sql = "update record set status = ? where sender = ?";
		Object [] objs = {RecordFlag.STATUS_READ,RecordFlag.STATUS_OFFICIAL};
		return modify(sql,objs);
	}

	@Override
	public int getFriendMsgNotRead(String uid) throws Exception {
		String sql="select count(*) as count from record where getter = ? and status = ? and sender <> ?";
		Object [] objs= {uid,RecordFlag.STATUS_NOTREAD,RecordFlag.STATUS_OFFICIAL};
		ResultSet rs=query(sql,objs);
		int count = 0;
		if(rs.next()) {
			count = rs.getInt("count");
		}
		return count;
	}

	

	



}
